package TimesheetManagemen;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class ProjectDAO {
	
	private Connection conn=null;

	public ProjectDAO() {
		String userName = "root";
		String password = "123456";
		String url = "jdbc:mysql://localhost/timesheet?"
				+ "user=root&password=123456";

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			conn = java.sql.DriverManager
					.getConnection(url, userName, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}}
	public int addProject(Project pj){
		String query="insert into project values(?,?,?)";
		int kq=0;
		try {
			PreparedStatement ps = conn.prepareStatement(query);
			ps.setInt(1, pj.getId());
			ps.setString(2, pj.getNameProject());
			ps.setInt(3, pj.getTimespend());
			kq= ps.executeUpdate();
			return kq;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return kq;
	}
	public void addPj(Project pj){
		ProjectDAO pjo=new ProjectDAO();
		 ArrayList<Project> list = pjo.getProject();
		    int a=0;
			int b=0;
			for(int i=0;i<list.size();i++)
			{
				int c=list.get(i).getId();
				String d=list.get(i).getNameProject();
				if(c==pj.getId())
				{a=1;
				break;}
				if(pj.getNameProject().equals(d)){
					b=1;
					break;
				}
			}
			 if( a==1){
			    	
					System.out.println("id exist");	
			    }
			 else if(b==1){
				 System.out.println("Name exist");
			 }
			     else if(pjo.addProject(pj)==1) {
			     	System.out.println("Add Successfull");
			    	
			}
			     else if(pjo.addProject(pj)==0 ){
						System.out.println("Not Add Successfull");
				     }   
	}
	public int updateProject(Project pj){
		String query="update project set nameProject=?, timespend=? where id=?";
		int kq=0;
		try {
			PreparedStatement ps = conn.prepareStatement(query);
			ps.setString(1, pj.getNameProject());
			ps.setInt(2, pj.getTimespend());
			ps.setInt(3, pj.getId());
			kq= ps.executeUpdate();
			return kq;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return kq;
	}
	public void updatePj(Project pj){
		ProjectDAO pjo=new ProjectDAO();
		 ArrayList<Project> list = pjo.getProject();
			int a=0;
			for(int i=0;i<list.size();i++){
				int c=list.get(i).getId();		
			if(c==pj.getId()){
				a=1;break;
			}}
			if(a==1){	 
			int kq = pjo.updateProject(pj);
		    if(kq!=0){
		    	System.out.print("Update Successful");
			}else{
				System.out.println("Not Update Successfull");
			}}
			else{
				System.out.println("Not Found Id");
		    }}
	public int deleteProject(int id){
		String query="delete from project where id=?";
		int kq=0;
		try {
			PreparedStatement ps = conn.prepareStatement(query);
			ps.setInt(1, id);
			kq= ps.executeUpdate();
			return kq;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return kq;
	}
	public ArrayList<Project> getProject(){
		ArrayList<Project> dspj = new ArrayList<>();
		
		String query = "SELECT * FROM project";
		
		try {
			PreparedStatement ps = conn.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()){
				int id = rs.getInt(1);
				String nameProject = rs.getString(2);
				int timespend = rs.getInt(3);
				Project pj = new Project(id,nameProject,timespend);
				dspj.add(pj);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		return dspj;
	}
	

}
